package com.tyk.demo.controller;

import com.tyk.demo.dto.UserInfo;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

@RequestMapping("/excel")
@Controller
public class ExcelController {
    @GetMapping("/export")
    public void excelExport(HttpServletResponse response) throws IOException {
        Workbook workbook = createExcel();
        String fileName = "test.xls";
        //
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName );
        response.setContentType("application/vnd.ms-excel;charset=gb2312");
        OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());

        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 创建excel
     * @return
     */
    public static Workbook createExcel() {
        //new HSSFWorkbook() make xls
        //new XSSFWorkbook() make xlsx
        Workbook xlsx = new HSSFWorkbook();

        Sheet sheet = xlsx.createSheet();
        Row row = sheet.createRow(1);
        Cell cell0 = row.createCell(0);
        cell0.setCellValue("0");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("1");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("2");
        return xlsx;
    }

    @PostMapping("/import")
    public ResponseEntity<String> excelImport(MultipartFile file) throws IOException {
        String msg = null;
        if (file.isEmpty()) {
            msg = "文件上传失败，请重新上传";
        }
        List<UserInfo> userInfoList = new ArrayList<>();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        UserInfo userInfo = null;
        for (int i = 0; i < rows; i++) {
            userInfo = new UserInfo();
            HSSFRow row = sheet.getRow(i);
            userInfo.setUserId(row.getCell(0).getStringCellValue());
            userInfo.setUsername(row.getCell(1).getStringCellValue());
//            userInfo.setAddtime(row.getCell(3).getStringCellValue());
            userInfoList.add(userInfo);
        }
        // 存入数据库
        msg = "导入成功！！！";
        return new ResponseEntity<String>(msg, HttpStatus.OK);
    }
}
